﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using ReferenciasSibs.DataSet;
using E4RPTools;

namespace ReferenciasSibs
{
    public class Database
    {
        public static SerieDataSet GetSerials(SqlTransaction sqlTransaction)
        {
            var dataSet = new SerieDataSet();
            const string query = "select distinct serie from seriesvendas order by 1";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Serie, query);
            return dataSet;
        }

        public static TipoDocumentoDataSet GetDocumentTypes(SqlTransaction sqlTransaction)
        {
            var dataSet = new TipoDocumentoDataSet();
            const string query = "select documento, descricao from documentosvenda order by descricao";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoDocumento, query);
            return dataSet;
        }

        public static CondominioDataSet GetHouseRentals(SqlTransaction sqlTransaction)
        {
            var dataSet = new CondominioDataSet();
            const string query =
                @"  select	Codigo as id, 
		                    IDNome as nome
                    from	empresas
                    where	IDAndarSala = '1'
                    order by IDNome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Condominio, query);
            return dataSet;
        }

        public static DocumentoDataSet GetDocumentsToReference(SqlTransaction sqlTransaction, string company, string companyName, string serial, string documentType, 
            DateTime beginDate, DateTime endDate)
        {
            var dataSet = new DocumentoDataSet();
            var query =
                @"  select	'" + company + @"' as Empresa, 
		                    Filial, 
                            Serie, 
                            TipoDoc as Tipo,
                            NumDoc as Numero,
                            TotalMerc -TotalDesc + TotalIva + TotalOutros as ValorComIva,
                            Data,
                            '' as Referencia,
                            -1 as IdReferencia,
                            '" + companyName + @"' as NomeEmpresa,   
                            c.Entidade as Cliente,
                            cl.Nome as NomeCliente                                
                    from	CabecDoc c, Clientes cl
                    where	cl.Cliente = c.Entidade
                            and c.Serie = @Serie
                            and c.TipoDoc = @TipoDocumento
                            and c.Data >= @DataInicial
                            and c.Data <= @DataFinal
                            and c.CDU_ReferenciaSibs is null 
                            and round(c.TotalMerc - c.TotalDesc + c.TotalIva + c.TotalOutros, 2) > (
			                    select	IsNull(Round(sum(ValorRec), 2), 0)
			                    from	LinhasLiq lr
			                    where	lr.FilialOrig = c.Filial
					                    and lr.SerieOrig = c.Serie
					                    and lr.TipoDocOrig = c.TipoDoc
					                    and lr.NumDocOrigInt = c.NumDoc)
                    union all
                    select	'" + company + @"' as Empresa, 
		                    Filial, 
                            Serie, 
                            TipoDoc as Tipo,
                            NumDoc as Numero,
                            TotalMerc -TotalDesc + TotalIva + TotalOutros as ValorComIva,
                            Data,
                            r.CDU_Referencia as Referencia,
                            r.CDU_Id as IdReferencia,
                            '" + companyName + @"' as NomeEmpresa,   
                            c.Entidade as Cliente,
                            cl.Nome as NomeCliente                                    
                    from	CabecDoc c, Clientes cl, PRIEMPRE.dbo.TDU_ReferenciasSibs r
                    where	cl.Cliente = c.Entidade
                            and c.Serie = @Serie
		                    and c.TipoDoc = @TipoDocumento
                            and c.Data >= @DataInicial
                            and c.Data <= @DataFinal
                            and round(c.TotalMerc - c.TotalDesc + c.TotalIva + c.TotalOutros, 2) > (
			                    select	IsNull(Round(sum(ValorRec), 2), 0)
			                    from	LinhasLiq lr
			                    where	lr.FilialOrig = c.Filial
					                    and lr.SerieOrig = c.Serie
					                    and lr.TipoDocOrig = c.TipoDoc
					                    and lr.NumDocOrigInt = c.NumDoc)		
                            and r.CDU_Referencia = c.CDU_ReferenciaSibs
                            and r.CDU_Filial = c.Filial
                            and r.CDU_Serie = c.Serie
                            and r.CDU_TipoDocumento = c.TipoDoc
                            and r.CDU_NumeroDocumento = c.NumDoc
                            and round(r.CDU_ValorComIva, 2) != round(c.TotalMerc - c.TotalDesc + c.TotalIva + c.TotalOutros, 2)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Serie", serial),
                                        new SqlParameter("TipoDocumento", documentType),
                                        new SqlParameter("DataInicial", beginDate),
                                        new SqlParameter("DataFinal", endDate)
                                    };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Documento, query, sqlParameters.ToArray());
            return dataSet;
        }

        public static long GetNextReferenceId(SqlTransaction sqlTransaction)
        {
            long referenceId;
            const string query = 
                @"  select case when max(CDU_Id) is null then 0 else max(CDU_Id) end
                    from   TDU_ReferenciasSibs";
            using (var dataAccess = new SqlDataAccess())
                referenceId = dataAccess.ExecuteScalar<long>(sqlTransaction, 0, query);
            return referenceId + 1;
        }

        public static void InsertReference(SqlTransaction sqlTransaction, long referenceId, string company, string subsidiary, string serial, string documentType, long documentNumber, 
            decimal valueWithVat, string reference, string companyName, string client, string clientName)
        {
            const string query =
                @"  insert 
                    into    TDU_ReferenciasSibs(CDU_Id, CDU_Referencia, CDU_Empresa, CDU_Filial, CDU_Serie, CDU_TipoDocumento, CDU_NumeroDocumento, CDU_ValorComIva, CDU_Enviado,
                                CDU_NomeEmpresa, CDU_Cliente, CDU_NomeCliente) 
                    values  (@Id, @Referencia, @Empresa, @Filial, @Serie, @TipoDocumento, @NumeroDocumento, @ValorComIva, @Enviado, @NomeEmpresa, @Cliente, @NomeCliente)";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Id", referenceId),
                                                new SqlParameter("Referencia", reference),
                                                new SqlParameter("Empresa", company),
                                                new SqlParameter("Filial", subsidiary),
                                                new SqlParameter("Serie", serial),
                                                new SqlParameter("TipoDocumento", documentType),
                                                new SqlParameter("NumeroDocumento", documentNumber),
                                                new SqlParameter("ValorComIva", valueWithVat),
                                                new SqlParameter("Enviado", false),
                                                new SqlParameter("NomeEmpresa", companyName),
                                                new SqlParameter("Cliente", client),
                                                new SqlParameter("NomeCliente", clientName)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateReference(SqlTransaction sqlTransaction, long referenceId, string reference, decimal valueWithVat, string companyName, string client, string clientName)
        {
            const string query =
                @"  update  TDU_ReferenciasSibs
                    set     CDU_Referencia = @Referencia,
                            CDU_ValorComIva = @ValorComIva,
                            CDU_Enviado = @Enviado,
                            CDU_NomeEmpresa = @NomeEmpresa, 
                            CDU_Cliente = @Cliente, 
                            CDU_NomeCliente = @NomeCliente
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Id", referenceId),
                                                new SqlParameter("Referencia", reference),
                                                new SqlParameter("ValorComIva", valueWithVat),
                                                new SqlParameter("Enviado", false),
                                                new SqlParameter("NomeEmpresa", companyName),
                                                new SqlParameter("Cliente", client),
                                                new SqlParameter("NomeCliente", clientName)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateReference(SqlTransaction sqlTransaction, string subsidiary, string serial, string documentType, long documentNumber, string reference, long entityNumber)
        {
            const string query =
                @"  update  CabecDoc 
                    set     CDU_ReferenciaSibs = @Referencia, 
                            CDU_NumeroEntidade = @NumeroEntidade
                    where   Filial = @Filial 
                            and Serie = @Serie
                            and TipoDoc = @TipoDocumento
                            and NumDoc = @NumeroDocumento";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Filial", subsidiary),
                                                new SqlParameter("Serie", serial),
                                                new SqlParameter("TipoDocumento", documentType),
                                                new SqlParameter("NumeroDocumento", documentNumber),
                                                new SqlParameter("Referencia", reference),
                                                new SqlParameter("NumeroEntidade", entityNumber)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static DocumentoDataSet GetDocumentsToSend(SqlTransaction sqlTransaction, string company, bool sent, bool notSent, DateTime beginDate, DateTime endDate)
        {
            var dataSet = new DocumentoDataSet();
            const string query =
                @"  select	r.CDU_Empresa as Empresa,                                 
		                    c.Filial, 
                            c.Serie, 
                            c.TipoDoc as Tipo,
                            c.NumDoc as Numero,
                            TotalMerc - TotalDesc + TotalIva + TotalOutros as ValorComIva,
                            c.Data,
                            r.CDU_Referencia as Referencia,
                            r.CDU_Id as IdReferencia,
                            r.CDU_NomeEmpresa as NomeEmpresa,
                            r.CDU_Cliente as Cliente,
                            r.CDU_NomeCliente as NomeCliente
                    from	CabecDoc c, priempre.dbo.TDU_ReferenciasSibs r
                    where	r.CDU_Serie = c.Serie
                            and r.CDU_TipoDocumento = c.TipoDoc
                            and r.CDU_NumeroDocumento = c.NumDoc
                            and r.CDU_Filial = c.Filial
                            and r.CDU_Empresa = @Empresa
                            and Data >= @DataInicial
                            and Data <= @DataFinal
                            {0}
                    order by r.CDU_Referencia";

            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Empresa", company),
                                        new SqlParameter("DataInicial", beginDate),
                                        new SqlParameter("DataFinal", endDate)
                                    };
            var filter = "";
            if(sent && !notSent) filter += "and CDU_Enviado = 1";
            if(!sent && notSent) filter += "and CDU_Enviado = 0";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Documento, string.Format(query, filter), sqlParameters.ToArray());
            return dataSet;
        }

        public static long GetNextFileSequence(SqlTransaction sqlTransaction, string date)
        {
            long sequence;
            const string query = 
                @"  select max(CDU_Sequencia)
                    from   TDU_FicheiroSibs
                    where  CDU_Data = @Data";
            using (var dataAccess = new SqlDataAccess())
                sequence = dataAccess.ExecuteScalar<long>(sqlTransaction, 0, query, new SqlParameter("Data", date));
            return sequence + 1;
        }

        public static string GetLastFileSent(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select CDU_Nome
                    from   TDU_FicheiroSibs
                    where  CDU_Id = (select max(CDU_Id) from TDU_FicheiroSibs)";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "000000000", query);
        }

        public static void InsertFile(SqlTransaction sqlTransaction, string name, string date, long sequence)
        {
            const string query =
                @"  insert 
                    into    TDU_FicheiroSibs(CDU_Nome, CDU_Data, CDU_Sequencia) 
                    values  (@Nome, @Data, @Sequencia)";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Nome", name),
                                                new SqlParameter("Data", date),
                                                new SqlParameter("Sequencia", sequence)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray()); 
        }

        public static void SetAsSent(SqlTransaction sqlTransaction, long referenceId)
        {
            const string query =
                @"  update  TDU_ReferenciasSibs
                    set     CDU_Enviado = 1
                    where   CDU_Id = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Id", referenceId));
        }

        public static long GetDocumentReferenceId(SqlTransaction sqlTransaction, string company, string subsidiary, string serial, string documentType, long documentNumber)
        {
            const string query =
                @"  select  CDU_Id
                    from    TDU_ReferenciasSibs
                    where   CDU_Empresa = @Empresa
                            and CDU_Filial = @Filial
                            and CDU_Serie = @Serie
                            and CDU_TipoDocumento = @TipoDocumento
                            and CDU_NumeroDocumento = @NumeroDocumento";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Empresa", company),
                                        new SqlParameter("Filial", subsidiary),
                                        new SqlParameter("Serie", serial),
                                        new SqlParameter("TipoDocumento", documentType),
                                        new SqlParameter("NumeroDocumento", documentNumber)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query, sqlParameters.ToArray());
        }

        public static void GetDocumentByReference(SqlTransaction sqlTransaction, string reference, DocumentoDataSet dataSet)
        {            
            const string query =
                @"  select  CDU_Id as IdReferencia, 
	                        CDU_Referencia as Referencia,
	                        CDU_Empresa as Empresa,
	                        CDU_Filial as Filial,
	                        CDU_Serie as Serie,
	                        CDU_TipoDocumento as Tipo,
	                        CDU_NumeroDocumento as Numero,
                            CDU_ValorComIva as ValorComIva,
                            CDU_NomeEmpresa as NomeEmpresa,
                            CDU_Cliente as Cliente,
                            CDU_NomeCliente as NomeCliente
                    from    TDU_ReferenciasSibs 
                    where   SUBSTRING(CDU_Referencia, 1, 7) = SUBSTRING(@Reference, 1, 7)";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill(sqlTransaction, dataSet.Documento, query, new SqlParameter("Reference", reference));            
        }

        public static string GetHouseRentalName(SqlTransaction sqlTransaction, string houseRentalId)
        {
            const string query =
                @"  select  IdNome
                    from    Empresas
                    where   Codigo = @HouseRentalId";
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("HouseRentalId", houseRentalId));
        }

        public static DocumentoDataSet GetDocumentData(SqlTransaction sqlTransaction, string company, string companyName, string reference)
        {
            var dataSet = new DocumentoDataSet();
            var query =
                @"  select	'" + company + @"' as Empresa, 
		                    Filial, 
                            Serie, 
                            TipoDoc as Tipo,
                            NumDoc as Numero,
                            TotalMerc -TotalDesc + TotalIva + TotalOutros as ValorComIva,
                            Data,
                            c.CDU_ReferenciaSibs as Referencia,
                            -1 as IdReferencia,
                            '" + companyName + @"' as NomeEmpresa,   
                            c.Entidade as Cliente,
                            cl.Nome as NomeCliente                                    
                    from	CabecDoc c, Clientes cl
                    where	cl.Cliente = c.Entidade
                            and c.CDU_ReferenciaSibs = @Referencia";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Documento, query, new SqlParameter("Referencia", reference));
            return dataSet;
        }

        public static DateTime GetDocumentDate(SqlTransaction sqlTransaction, string company, string reference)
        {
            const string query =
                @"  select	Data                            
                    from	CabecDoc
                    where	CDU_ReferenciaSibs = @Referencia";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, DateTime.MinValue, query, new SqlParameter("Referencia", reference));            
        }

        public static void UpdateCompanyData(SqlTransaction sqlTransaction, string reference, string companyName)
        {
            const string query =
                @"  update  TDU_ReferenciasSibs 
                    set     CDU_NomeEmpresa = @NomeEmpresa
                    where   CDU_Referencia = @Referencia";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Referencia", reference),
                                                new SqlParameter("NomeEmpresa", companyName)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateClientData(SqlTransaction sqlTransaction, string reference, string client, string clientName)
        {
            const string query =
                @"  update  TDU_ReferenciasSibs 
                    set     CDU_Cliente = @Cliente, 
                            CDU_NomeCliente = @NomeCliente
                    where   CDU_Referencia = @Referencia";
            var sqlParameters = new List<SqlParameter>
                                            {
                                                new SqlParameter("Referencia", reference),
                                                new SqlParameter("Cliente", client),
                                                new SqlParameter("NomeCliente", clientName)
                                            };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

    }
}
